package celcatSynchronizer;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Export au format CSV.
 */
public class CsvExporter
{
	/**
	 * Si une virgule se trouve dans la string, on la quote.
	 * @param p_element La string.
	 * @return La string quotée.
	 */
	private String quoteCsvElement(String p_element)
	{
		if(p_element == null)
			return "";
		else if(p_element.contains(","))
			return "\"" + p_element.replace("\"", "\\\"") + "\"";
		
		return p_element;
	}

	/**
	 * Ecrit une ligne de ressources pour un event.
	 * @param p_name Le nom de la ressource.
	 * @param p_rs Le ResultSet.
	 * @param p_out Le fichier CSV.
	 */
	private void writeCsvResources(String p_name, ResultSet p_rs, FileWriter p_out) throws IOException, SQLException
	{
		while(p_rs.next())
		{
			p_out.write(",,,,,,,,,,,,,,,,,," + p_name + ",");
			p_out.write(quoteCsvElement(p_rs.getString("_unique_name")) + ",");
			p_out.write(quoteCsvElement(p_rs.getString("_resWeeks")) + ",");
			p_out.write(quoteCsvElement(p_rs.getString("_layoutName")) + ",");
			p_out.write(quoteCsvElement(p_rs.getString("_scheduling_status")) + ",");
			p_out.write(quoteCsvElement(p_rs.getString("_quantity")) + "\r\n");
		}
	}
	
	/**
	 * Ecrit les semaines dans le CSV.
	 * @param p_rs Le ResultSet des semaines.
	 * @param p_out Le fichier CSV.
	 */
	private void writeCsvResourcesWeek(ResultSet p_rs, FileWriter p_out) throws IOException, SQLException
	{
		StringBuilder data = new StringBuilder();
		if(p_rs.next())
		{
			data.append(String.valueOf(p_rs.getInt("_week")));
			while(p_rs.next())
			{
				data.append(",");
				data.append(String.valueOf(p_rs.getInt("_week")));
			}
		}
		
		p_out.write(quoteCsvElement(data.toString()) + ",");
	}

	/**
	 * Export de la db en CSV.
	 * @param p_db La db.
	 * @param p_csvFile Le fichier CSV.
	 * @param p_groupId L'id du groupe sélectionné.
	 */
	public void exportDb(Connection p_db, File p_csvFile, int p_groupId) throws IOException, SQLException
	{
		PreparedStatement statEvents = p_db.prepareStatement("SELECT CT_EVENT._event_id, _day_of_week, _start_time, _end_time, _break_mins, _spanName, _event_catName, _tag1, _tag2, _capacity_req, _deptName, _global_event, _protected, _suspended, _grouping_id, _registers_req, _lock FROM CT_EVENT, CT_EVENT_GROUP WHERE CT_EVENT_GROUP._event_id = CT_EVENT._event_id AND _group_id = ?");
		statEvents.setInt(1, p_groupId);
		ResultSet resEvent = statEvents.executeQuery();
		
		PreparedStatement statEventWeek = p_db.prepareStatement("SELECT _week FROM CT_EVENT_WEEK WHERE CT_EVENT_WEEK._event_id = ?");
		
		// Creation du fichier en ecriture si il existe on l'ecrase
		FileWriter out = new FileWriter(p_csvFile, false);
		
		// Ecriture du header du fichier CT_EVENT
		// ------- Utile ?
		out.write("CT_EVENT\r\n_event_id,_day_of_week,_start_time,_end_time,_weeks,_break_mins,_spanName,_event_catName,_tag1,_tag2,_capacity_req,_deptName,_global_event,_protected,_suspended,_grouping_id,_registers_req,_lock,_resType,_resName,_resWeeks,_layoutName,_scheduling_status,_quantity\r\n");
		
		// On parcourt toute les données de la table
		while(resEvent.next())
		{
			int eventId = resEvent.getInt("_event_id");
						
			//Ecriture des données dans le fichier CSV
			out.write(quoteCsvElement(String.valueOf(eventId)) + ",");
			out.write(quoteCsvElement(resEvent.getString("_day_of_week")) + ",");
			
			int startTime = resEvent.getInt("_start_time");
			int endTime = resEvent.getInt("_end_time");
			out.write(String.format("%02d:%02d,%02d:%02d,", startTime / 60, startTime % 60, endTime / 60, endTime % 60));
			
			// Weeks
			statEventWeek.setInt(1, eventId);
			writeCsvResourcesWeek(statEventWeek.executeQuery(), out);
			
			out.write(quoteCsvElement(resEvent.getString("_break_mins")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_spanName")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_event_catName")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_tag1")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_tag2")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_capacity_req")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_deptName")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_global_event")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_protected")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_suspended")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_grouping_id")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_registers_req")) + ",");
			out.write(quoteCsvElement(resEvent.getString("_lock")) + "\r\n");
			
			// Module resources
			PreparedStatement statEventModule = p_db.prepareStatement("SELECT _unique_name, _resWeeks, _layoutName, _scheduling_status, _quantity FROM CT_MODULE, CT_EVENT_MODULE WHERE CT_MODULE._module_id = CT_EVENT_MODULE._module_id AND _event_id = ?");
			statEventModule.setInt(1, eventId);
			writeCsvResources("Module", statEventModule.executeQuery(), out);
			
			// Room resources
			PreparedStatement statEventRoom = p_db.prepareStatement("SELECT _unique_name, _resWeeks, _layoutName, _scheduling_status, _quantity FROM CT_ROOM, CT_EVENT_ROOM WHERE CT_ROOM._room_id = CT_EVENT_ROOM._room_id AND _event_id = ?");
			statEventRoom.setInt(1, eventId);
			writeCsvResources("Room", statEventRoom.executeQuery(), out);
			
			// Group resources
			PreparedStatement statEventGroup = p_db.prepareStatement("SELECT _unique_name, _resWeeks, _layoutName, _scheduling_status, _quantity FROM CT_GROUP, CT_EVENT_GROUP WHERE CT_GROUP._group_id = CT_EVENT_GROUP._group_id AND _event_id = ?");
			statEventGroup.setInt(1, eventId);
			writeCsvResources("Group", statEventGroup.executeQuery(), out);
			
			// Staff resources
			PreparedStatement statEventStaff = p_db.prepareStatement("SELECT _unique_name, _resWeeks, _layoutName, _scheduling_status, _quantity FROM CT_STAFF, CT_EVENT_STAFF WHERE CT_STAFF._staff_id = CT_EVENT_STAFF._staff_id AND _event_id = ?");
			statEventStaff.setInt(1, eventId);
			writeCsvResources("Staff", statEventStaff.executeQuery(), out);
		}
		
		//Fermeture du fichier
		out.close();
	}
}
